package com.wxtf.platform.main.report;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;

/**
 * <li>Title: monthUsageReport.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.report</li>
 * <li>Description: 月药具使用情况报告单</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author zhanqiulin
 * @version 1.0.0.0
 * 
 */
public class MonthUsageReport extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(MonthUsageReport.class);

	public MonthUsageReport(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("selectAssess".equals(this.getMethodName())) {
				msg = this.selectAssess(this.getLogicData());
				return msg;
			} else if("selectDetail".equals(this.getMethodName())) {
				msg = this.selectDetail(this.getLogicData());
				return msg;
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("expxls".equals(this.getMethodName())) {
				msg = this.expxls(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {"tmapm_id","tbe_id","org_id","tbe_name","tbe_address","tbe_age","tbe_position","tbe_qualification","tbfi_children_num","tmad_name","tmapm_begin_time","tmapm_reason","tmapm_last_medicine","tmapm_is_fail","tmapm_fail_reason","tmad_unit","tmapd_num","tmapm_style","tbe_spell_code","reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	String[] fields1 = {
			"tmamr_id","org_id","tmamr_matron_num","tmamr_women_fertility_num",
			"tmamr_ligation_num","tmamr_sheung_wan_num","tmamr_skinburied_num","tmamr_keep_certificate_num","tmamr_thismonth_birth_num",
			"tmamr_unmarry_num","tmamr_widowed_num","tmamr_barrenness_num","tmamr_menopause_num","tmamr_director","reserve1","reserve2",
			"reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time","tmamr_date"};
	@Override
	public Object select(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String reqdate = "";
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			reqdate = jsonObject.getString("reportdate");//日期
		}
		if("".equals(reqdate)){
			Calendar cal = Calendar.getInstance();
			SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM");
			reqdate = format1.format(cal.getTime());
		}
		if("0".equals(user.getUserId())) {
			sql = " from tma_provide_main a left join tma_provide_detail b on a.tmapm_id = b.tmapm_id left join tma_dictionary c on b.tmad_id = c.tmad_id left join tb_employee d on a.tbe_id = d.tbe_id left join tb_family_base e on a.tbe_id = e.tbe_id where a.reserve2='1' and a.tmapm_time like '"+reqdate+"%'";
		} else {
			sql = " from tma_provide_main a left join tma_provide_detail b on a.tmapm_id = b.tmapm_id left join tma_dictionary c on b.tmad_id = c.tmad_id left join tb_employee d on a.tbe_id = d.tbe_id left join tb_family_base e on a.tbe_id = e.tbe_id where a.reserve2='1' and a.tmapm_time like '"+reqdate+"%' and a.org_id in (" + user.getCurSubOrgId() + ")";
		}
		Object[] params = null;
		String countSql = "select count(*) " + sql;
		String querySql = "";
		if("oracle".endsWith(user.getDbType())){
			querySql = "select a.tmapm_id,a.tbe_id,a.org_id,d.tbe_name,d.tbe_address,floor(months_between(SYSDATE,to_date(d.TBE_BIRTHDAY,'yyyy-mm-dd'))/12) AS age,d.tbe_position,d.tbe_qualification,e.tbfi_children_num,c.tmad_name,a.tmapm_begin_time,a.tmapm_reason,a.tmapm_last_medicine,a.tmapm_is_fail,a.tmapm_fail_reason,c.tmad_unit,b.tmapd_num,a.tmapm_style,d.tbe_spell_code,a.reserve1,a.reserve2,a.reserve3,a.reserve4,a.reserve5,a.create_by,a.create_time,a.last_modify_by,a.last_modify_time " 
				+ sql+" order by a.last_modify_time desc";
		}else if("mysql".endsWith(user.getDbType())){
			querySql = "select a.tmapm_id,a.tbe_id,a.org_id,d.tbe_name,d.tbe_address,(YEAR(CURDATE())-YEAR(d.TBE_BIRTHDAY))-(RIGHT(CURDATE(),5)<RIGHT(d.TBE_BIRTHDAY,5)) AS age,d.tbe_position,d.tbe_qualification,e.tbfi_children_num,c.tmad_name,a.tmapm_begin_time,a.tmapm_reason,a.tmapm_last_medicine,a.tmapm_is_fail,a.tmapm_fail_reason,c.tmad_unit,b.tmapd_num,a.tmapm_style,d.tbe_spell_code,a.reserve1,a.reserve2,a.reserve3,a.reserve4,a.reserve5,a.create_by,a.create_time,a.last_modify_by,a.last_modify_time " 
				+ sql+" order by a.last_modify_time desc";
		}
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff1 = new StringBuffer();
		List<Object[]> list = new ArrayList<Object[]>();
		String rdate =  jsonObject.getString("ryear")+"-"+jsonObject.getString("rmonth")+"-"+jsonObject.getString("rday");
		buff1.append("select tmamr_id,org_id from tma_month_usage_report where org_id='").append(user.getCurOrgId()).append("' and tmamr_date like '")
		.append(jsonObject.getString("ryear")+"-"+jsonObject.getString("rmonth")).append("%'");
		list = AccessDataBaseUtil.query(buff1.toString(), null);
		if(list.size() > 0){
			return "\"本月已上报！\"";
		}
		buff1.setLength(0);
		buff1.append("insert into tma_month_usage_report(tmamr_id,org_id");
		Object[] obj = new Object[fields1.length];
		for (int j = 2; j < fields1.length; j++) {
			obj[j] = jsonObject.getString(fields1[j]);
			buff1.append(",").append(fields1[j]);
		}
		buff1.append(") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		obj[1] = user.getCurOrgId();
		String curTime = FormatUtil.getCurentDateTime();
		obj[fields1.length-1] = rdate;
		obj[fields1.length-5] = user.getUserId();
		obj[fields1.length-4] = curTime;
		obj[fields1.length-3] = user.getUserId();
		obj[fields1.length-2] = curTime;
		
		AccessDataBaseUtil.executeSQLUpdate(buff1.toString(), obj);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("update tma_month_usage_report set ");
		Object[] obj = new Object[fields1.length];
		for(int j = 1; j < fields1.length; j++) {
			obj[j - 1] = jsonObject.getString(fields1[j]);
			buff.append(fields1[j]).append(" = ?,");
		}
		buff.deleteCharAt(buff.length() - 1).append(" where tmamr_id = ?");
		String rdate =  jsonObject.getString("ryear")+"-"+jsonObject.getString("rmonth")+"-"+jsonObject.getString("rday");
		obj[fields1.length-2] = rdate;
		obj[fields1.length-4] = user.getUserId();
		obj[fields1.length-3] = curTime;
		obj[fields1.length-1] = jsonObject.getString("tmamr_id");
		
		AccessDataBaseUtil.executeSQLUpdate(buff.toString(), obj);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from tma_dictionary where tmad_id = ?";
		
		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"删除成功\"";
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	/**
	 * 查询明细
	 * @param jsonArray
	 * @return
	 */
	public Object selectDetail(Object jsonArray) {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		StringBuffer querySql = new StringBuffer();
		
//		if("0".equals(user.getUserId())){
//			sql.append(b) = " from tb_employee m left join tb_family_base n on m.tbe_id = n.tbe_id where 1 = 1";
//		} else {
//			sql = " from tb_employee m left join tb_family_base n on m.tbe_id = n.tbe_id where m.org_id in (" + user.getCurSubOrgId() + ") ";
//		}
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		
		String mindata,maxdata;
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String type = jsonObject.getString("type");
			String ryear = jsonObject.getString("ryear");
			if("".endsWith(ryear)){
				Calendar cal = Calendar.getInstance();
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				cal.add(Calendar.YEAR, -49);
				mindata = format.format(cal.getTime());
				cal = Calendar.getInstance();
				cal.add(Calendar.YEAR, -20);
				maxdata = format.format(cal.getTime());
			}else{
				Integer y = Integer.parseInt(ryear);
				mindata = (y-49)+"-"+jsonObject.getString("rmonth")+"-"+jsonObject.getString("rday");
				maxdata = (y-20)+"-"+jsonObject.getString("rmonth")+"-"+jsonObject.getString("rday");
			}
			if("tmamr_matron_num".equals(type)){
				querySql.append(" from tb_employee a where a.tbe_marital_status<>'8A90F0D0176C4961B322BEB26703A320' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5')")
				.append(" and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_women_fertility_num".equals(type)){
				/*已婚有生育条件育龄妇女*/
				querySql.append(" from tb_employee a where a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')")
				.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5')")
				.append(" and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_ligation_num".equals(type)){
				//已结扎
				querySql.append(" from tb_employee a where a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
				.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_birth_control_measures='104-1' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_sheung_wan_num".equals(type)){
				/*已上环*/
				querySql.append(" from tb_employee a where a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
				.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_birth_control_measures='104-2' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_skinburied_num".equals(type)){
				/*皮埋*/
				querySql.append(" from tb_employee a where a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
				.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_birth_control_measures='104-3' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_keep_certificate_num".equals(type)){
				/*持证已孕待孕*/
				querySql.append(" from tb_employee a inner join tb_family_base b on (a.tbe_id=b.tbe_id or a.tbe_id=b.tbe_spouse_id) and a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
				.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5') inner join tbe_pregnancy_record c on b.tbfi_id=c.tbfi_id and (c.tbepr_option='0' or c.tbepr_option='1') where 1=1");
			}else if("tmamr_thismonth_birth_num".equals(type)){
				/*本月出生*/
				querySql.append(" from tbe_neonate_main b where b.tbenm_birth_time like '").append("reportdate").append("%'");
			}else if("tmamr_unmarry_num".equals(type)){
				/*离婚*/
				querySql.append(" from tb_employee a where a.tbe_sex='女' and a.tbe_marital_status='8A90F0D0176C4961B322BEB26703A322' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
			}else if("tmamr_widowed_num".equals(type)){
				/*丧偶*/
				querySql.append(" from tb_employee a where a.tbe_sex='女' and a.tbe_marital_status='8A90F0D0176C4961B322BEB26703A323' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
		
			}else if("tmamr_barrenness_num".equals(type)){
				/*不孕症*/
				querySql.append(" from tb_employee a where a.tbe_sex='女' and a.tbe_is_barrenness='1' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<=='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
		
			}else if("tmamr_menopause_num".equals(type)){
				/*绝经*/
				querySql.append(" from tb_employee a where a.tbe_sex='女' and a.the_is_menopause='1' and a.tbe_status not in ('102-3','102-4','102-5') and a.tbe_birthday<=='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("'");
				
			}else{
				querySql.append(" from tb_employee a where 1=1 ");
			}
			if("0".equals(user.getUserId())){
			} else {
				querySql.append(" and a.org_id in (").append(user.getCurSubOrgId()).append(") ");
			}
			// 排序
			String sortname = this.getRequest().getParameter("sortname");
			String sortorder = this.getRequest().getParameter("sortorder");
			if(sortname != null && sortname != null) {
				querySql.append(" order by ").append(sortname).append(" ").append(sortorder);
			}
		}
		String[] fields2 = {
				"tbe_id","org_id","tbe_idcard","tbe_work_permit",
				"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
				"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
				"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
				"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
				"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo","reserve1"
		};
		String sql =("select a.tbe_id,a.org_id,a.tbe_idcard,a.tbe_work_permit,a.tbe_employment_forms,a.tbe_name,a.tbe_former_name,a.tbe_spell_code,a.tbe_sex,a.tbe_age,a.tbe_birthday,a.tbe_nation,a.tbe_native_palce,a.tbe_birthplace,a.tbe_join_time,a.tbe_seniority,a.tbe_family_phone,a.tbe_mobile,a.tbe_address,a.tbe_marital_status,a.tbe_marital_condition,a.tbe_married_time,a.tbe_marriage_certificate,a.tbe_political_status,a.tbe_qualification,a.tbe_graduate_college,a.tbe_major,a.tbe_work_area,a.tbe_position,a.tbe_is_again_birth,a.tbe_is_barrenness,a.the_is_menopause,a.tbe_is_late_marriage,a.tbe_is_late_birth,a.tbe_status,a.tbe_ba_status,a.tbe_birth_control_measures,a.tbe_es_time,a.tbe_is_es,a.tbe_es_reason,a.tbe_children_status,a.tbe_order,a.tbe_memo,a.reserve1")+querySql.toString();
		String countSql = "select count(a.tbe_id) "+querySql.toString();
		return AccessDataBaseUtil.query4SQLPagging(fields2, countSql, sql, this.getPage(), this.getPagesize(), null);
	}
	/**
	 * 查询统计值
	 * @param jsonArray
	 * @return
	 * @throws Exception 
	 */
	
	public Object selectAssess(Object jsonArray) throws Exception {
	
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray arr = new JSONArray();
		if("".equals(user.getUserId())){
			
		} else {
			String[] fields2 = {
					"tmamr_matron_num","tmamr_women_fertility_num",
					"tmamr_ligation_num","tmamr_sheung_wan_num","tmamr_skinburied_num","tmamr_keep_certificate_num","tmamr_thismonth_birth_num",
					"tmamr_unmarry_num","tmamr_widowed_num","tmamr_barrenness_num","tmamr_menopause_num"};
			JSONArray array = JSONArray.fromObject(jsonArray);
			Calendar cal;
			if(array.size() > 0) {
				StringBuffer querySql = new StringBuffer();
				JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
				String reqdate = jsonObject.getString("reportdate");//日期
				String reportdate;//日期
				List<?> list;
				if("".equals(reqdate)){
					cal = Calendar.getInstance();
					SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM");
					reportdate = format1.format(cal.getTime());
				}else{
					reportdate = reqdate;
				}
				querySql.append("select tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,")
						.append("tmamr_keep_certificate_num,tmamr_thismonth_birth_num,tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,")
						.append("reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date from tma_month_usage_report where tmamr_date like '")
						.append(reportdate).append("%' and org_id='").append(user.getCurOrgId()).append("'");
				list = AccessDataBaseUtil.query(fields1, querySql.toString(), null);
				if(list.size() == 0){
					SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
					Calendar cal1 = Calendar.getInstance();
					String[] ss = reportdate.split("-");
					cal1.set(Calendar.YEAR, Integer.parseInt(ss[0]));
					cal1.set(Calendar.MONTH, Integer.parseInt(ss[1])-1);
					if(StringUtils.isNotBlank(reqdate)){
						cal1.set(Calendar.DATE, cal1.get(Calendar.DATE));
					}
					cal = Calendar.getInstance();
					cal.setTime(cal1.getTime());
					cal.add(Calendar.YEAR, -49);
					String mindata = format.format(cal.getTime());
					cal.setTime(cal1.getTime());
					cal.add(Calendar.YEAR, -20);
					String maxdata =format.format(cal.getTime());
					querySql.setLength(0);
					querySql.append("select ");
					/*已婚育龄妇女*/
					querySql.append("(select count(tbe_id) from tb_employee where tbe_marital_status<>'8A90F0D0176C4961B322BEB26703A320' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId())
							.append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhylfv,")
					/*已婚有生育条件育龄妇女*/
							.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')")
							.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId())
							.append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhysytjylfv,")
					/*已结扎*/
							.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
							.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-1' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yjz,")
					/*已上环*/
							.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
							.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-2' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as ysh,")
					/*皮埋*/
							.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
							.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-3' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as pm,")
					/*持证已孕待孕*/
							.append("(select count(a.tbe_id) from tb_employee a inner join tb_family_base b on (a.tbe_id=b.tbe_id or a.tbe_id=b.tbe_spouse_id) and a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
							.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5') and a.org_id in (").append(user.getCurSubOrgId()).append(") and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("' inner join tbe_pregnancy_record c on b.tbfi_id=c.tbfi_id and (c.tbepr_option='0' or c.tbepr_option='1')) as czdydy,");
					/*本月出生*/
					if("oracle".endsWith(user.getDbType())){
						querySql.append("(select nvl(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id in (").append(user.getCurSubOrgId()).append(")) as bycs,");
					}else if("mysql".endsWith(user.getDbType())){
						
						querySql.append("(select ifnull(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id in (").append(user.getCurSubOrgId()).append(")) as bycs,");
					}
					/*离婚*/
					querySql.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A322' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as lh,")
					/*丧偶*/
							.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A323' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as so,")
					/*不孕症*/
							.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_is_barrenness='1' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as byz,")
					/*绝经*/
							.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and the_is_menopause='1' and tbe_status not in ('102-3','102-4','102-5' ) and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as jj");
					list = AccessDataBaseUtil.query(fields2, querySql.toString(), null);
				}
				
				if(list.size() > 0){
					arr = JSONArray.fromObject(list);
				}
			}
		}
		return arr.toString();
	}
	/**
	 * 导出excel
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object expxls(String jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String reqdate = "";
		JSONObject jsonObject = new JSONObject();
		if(array.size() > 0) {
			jsonObject = JSONObject.fromObject(array.getString(0));
			reqdate = jsonObject.getString("reportdate");//日期
		}
		if("".equals(reqdate)){
			return "\"日期不能为空\"";
		}
		if("0".equals(user.getUserId())) {
			sql = " from tma_provide_main a left join tma_provide_detail b on a.tmapm_id = b.tmapm_id left join tma_dictionary c on b.tmad_id = c.tmad_id left join tb_employee d on a.tbe_id = d.tbe_id left join tb_family_base e on a.tbe_id = e.tbe_id where a.reserve2='1' and a.tmapm_time like '"+reqdate+"%'";
		} else {
			sql = " from tma_provide_main a left join tma_provide_detail b on a.tmapm_id = b.tmapm_id left join tma_dictionary c on b.tmad_id = c.tmad_id left join tb_employee d on a.tbe_id = d.tbe_id left join tb_family_base e on a.tbe_id = e.tbe_id where a.reserve2='1' and a.tmapm_time like '"+reqdate+"%' and a.org_id in (" + user.getCurSubOrgId() + ")";
		}
		Object[] params = null;
		String querySql = "select d.tbe_name,d.tbe_address,d.tbe_age,d.tbe_spell_code,d.tbe_qualification,e.tbfi_children_num,c.tmad_name,a.tmapm_begin_time,a.tmapm_reason,a.tmapm_last_medicine,(case when a.tmapm_is_fail='1' then '有' else '无' end),a.tmapm_fail_reason,c.tmad_unit,b.tmapd_num,(case when a.tmapm_style='1' then '领用' else '发放' end)" 
			+ sql+" order by a.last_modify_time desc";
		List<Object[]> list = AccessDataBaseUtil.query(querySql, params);
		//XML文件所在真实路径
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		//模板文件所在位置
		String model = url + "/model/monthUsageReport.xls";
		String file = url + "/file/monthUsageReport.xls";
		//文件下载路径
		String rfile = "jsp/app/xls/file/monthUsageReport.xls";
//			//通过模板文件复制一份文件
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
//			
//			//设置非部门数据内容，如：日期、填表人、标题等
		String orgname = "填报单位：" + jsonObject.getString("org_name");                                                          
		String title = jsonObject.getString("title");
//			//判断是否有部门数据
		if(list.size() > 0){
//				//打开刚刚生成好的Excel文件并生成相应的POI类对象
			POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(fto));
			HSSFWorkbook book = new HSSFWorkbook(filein);
			HSSFSheet sheet = book.getSheet("药具使用发放");
//				//数据起始位置行号（前5行为表头，所以数据从第5行开始填入excel）
			int startRow = 4;
//				//定义POI的Excel行对象
			HSSFRow row = null;
//				//设置标题
			row = sheet.createRow(0);
			row.setHeight((short) 900);
			HSSFCell cell0_0 = row.createCell(0);
			HSSFCellStyle cstyle0 = book.createCellStyle();
			HSSFFont titleFont = book.createFont();
			titleFont.setFontName("黑体");
			titleFont.setFontHeightInPoints((short) 20);//设置字体大小
			titleFont.setUnderline((byte)1);
			cstyle0.setFont(titleFont);
			cstyle0.setAlignment(CellStyle.ALIGN_CENTER);
			cstyle0.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			cell0_0.setCellStyle(cstyle0);
			cell0_0.setCellValue(new HSSFRichTextString(title));
//				//单位
			row = sheet.createRow(1);
			row.setHeight((short) 500);
			HSSFCell cell1_0 = row.createCell(0);
			HSSFCellStyle cstyle1 = book.createCellStyle();
			cstyle1.setAlignment(CellStyle.ALIGN_LEFT);
			cstyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			cell1_0.setCellStyle(cstyle1);
			cell1_0.setCellValue(orgname);
			HSSFCellStyle borderStyle = book.createCellStyle();
			borderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
			borderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			borderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			borderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			//填写发放记录
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i + startRow);
				row.setHeight((short) 400);
				//序号
				HSSFCell cell = row.createCell(0);
				HSSFCellStyle cstyle = book.createCellStyle();
				cstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				cstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				cstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				cstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				cstyle.setAlignment(CellStyle.ALIGN_CENTER);
				cstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				cell.setCellStyle(cstyle);
				cell.setCellValue((i+1) + "");
				//取出每一行数据
				Object[] rowData = list.get(i);
				//循环每个数据列，并装填到excel单元格中
				for(int j = 0; j < rowData.length; j++) {
					cell = row.createCell(j + 1);
					cell.setCellStyle(cstyle);
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					cell.setCellValue(new HSSFRichTextString(rowData[j].toString()));
				}
			}
//				//填入统计数据
			int t = startRow + list.size();
			sheet.addMergedRegion(new CellRangeAddress(t,t,0,15));
			row = sheet.createRow(t);
			row.setHeight((short) 500);
			HSSFCell cCell = row.createCell(0);
			HSSFCellStyle cCstyle = book.createCellStyle();
			cCstyle.setAlignment(CellStyle.ALIGN_LEFT);
			cCstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			cCell.setCellStyle(cCstyle);
			StringBuffer buff = new StringBuffer();
			buff.append("注:(1)已婚育龄妇女(").append(jsonObject.getString("tmamr_matron_num")).append(")人，已婚有生育条件的育龄妇女(")
			.append(jsonObject.getString("tmamr_women_fertility_num")).append(")人，已结扎(").append(jsonObject.getString("tmamr_ligation_num"))
			.append(")人， 已上环(").append(jsonObject.getString("tmamr_sheung_wan_num")).append(")人， 皮埋(")
			.append(jsonObject.getString("tmamr_skinburied_num")).append(")人。");
			cCell.setCellValue(buff.toString());
			t += 1;
			sheet.addMergedRegion(new CellRangeAddress(t,t,0,15));
			row = sheet.createRow(t);
			row.setHeight((short) 500);
			cCell = row.createCell(0);
			cCell.setCellStyle(cCstyle);
			buff.setLength(0);
			buff.append("   (2)本月出生(").append(jsonObject.getString("tmamr_thismonth_birth_num")).append(")人， 已孕等孕(")
			.append(jsonObject.getString("tmamr_keep_certificate_num")).append(")人。");
			cCell.setCellValue(buff.toString());
			t += 1;
			sheet.addMergedRegion(new CellRangeAddress(t,t,0,15));
			row = sheet.createRow(t);
			row.setHeight((short) 500);
			cCell = row.createCell(0);
			cCell.setCellStyle(cCstyle);
			buff.setLength(0);
			buff.append("   (3)离婚(").append(jsonObject.getString("tmamr_unmarry_num")).append(")人，丧偶(").append(jsonObject.getString("tmamr_widowed_num"))
			.append(")人，不孕症(").append(jsonObject.getString("tmamr_barrenness_num")).append(")人， 绝经(").append(jsonObject.getString("tmamr_menopause_num")).append(")人。");
			cCell.setCellValue(buff.toString());
			t += 1;
			sheet.addMergedRegion(new CellRangeAddress(t,t,0,4));
			sheet.addMergedRegion(new CellRangeAddress(t,t,5,9));
			sheet.addMergedRegion(new CellRangeAddress(t,t,10,15));
			row = sheet.createRow(t);
			row.setHeight((short) 500);
			cCell = row.createCell(0);
			cCell.setCellStyle(cCstyle);
			buff.setLength(0);
			buff.append("单位负责人: ").append(jsonObject.getString("tmamr_director"));
			cCell.setCellValue(buff.toString());
			cCell = row.createCell(5);
			cCell.setCellStyle(cCstyle);
			buff.setLength(0);
			buff.append("填表人: ").append(user.getUserrelName());
			cCell.setCellValue(buff.toString());
			cCell = row.createCell(10);
			HSSFCellStyle cCstyle1 = book.createCellStyle();
			cCstyle1.setAlignment(CellStyle.ALIGN_RIGHT);
			cCstyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			cCell.setCellStyle(cCstyle1);
			buff.setLength(0);
			buff.append("填报日期: ").append(jsonObject.getString("tmamr_time"));
			cCell.setCellValue(buff.toString());
			//输出文件并关闭流
			FileOutputStream fileOut = new FileOutputStream(file);
			book.write(fileOut);
			fileOut.close();
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"报表中没有记录\"}";
		}
//		//将文件的下载地址路径返回
		return "\"" + rfile + "\"";
	}
}


